package tests;

import java.sql.*;
import java.util.Scanner;

public class MySQLConnector {
	final Scanner scanner;
	final FormatReader reader;
	final String url = "jdbc:MySQL://localhost:3306/film_sys" +
			"?useSSL=false&serverTimeZone=UTC",
			password = "A7btngqxA7btngqx";
	private final Connection connection;
	public String[] cols = {"name", "type", "actor", "year",
			"avg_rating", "rating_num"},
			cols_zh = {"1.电影名称", "2.类型", "3.主演", "4.上映年份", "5.评价", "6.评价人数"},
			types = {"1.动作片", "2.喜剧片", "3.科幻片"};

	MySQLConnector(Scanner scanner) throws Exception {
		this.scanner = scanner;
		this.reader = new FormatReader(scanner);
		connection = DriverManager.getConnection(url, "root", password);
	}

	public void adminMenu() {
		String[] choices = {"1.添加电影", "2.查询电影", "3.删除电影", "0.退出"};
		String trial;
		do{
			trial=reader.readStr("请输入管理员密码(输入0退出)");
			if(trial.equals(password))break;
			else if (trial.equals("0")) return;
			System.out.println("密码错误");
		}while (true);
		System.out.println("**管理员登陆成功**");
		while (true) {
			try {
				switch (reader.selectMenu(choices, true,true)) {
					case 1:
						add();
						continue;
					case 2:
						query();
						continue;
					case 3:
						delete();
						continue;
					case 0:
						return;
				}
			} catch (Exception e) {
				System.out.println("数据库更新失败");
			}
		}
	}

	public void userMenu() {
		String[] choices = {"1.按条件推荐", "2.评分", "3.查看所有推荐", "0.返回上一界面"};
		while (true) try {
			System.out.println("****请选择后续操作****");
			switch (reader.selectMenu(choices, true, true)) {
				case 0:
					break;
				case 1:
					int type, orderBy;
					boolean normalExit = false;
					while (!normalExit) {
						System.out.println("请选择电影类型和排序方式（默认按电影名称排序）");
						orderBy = reader.selectMenu(types, false, false);
						type = reader.selectMenu(cols_zh, false, false);
						try {
							query(type, orderBy);
							normalExit = true;
						} catch (RuntimeException e) {
							System.out.println("按条件查询失败,是否再试");
							if (reader.readStr("按0退出").equals("0"))
								query();
							else continue;
						}
					}
					break;
				case 2:
					rate();
					continue;
				case 3:
					query();
					continue;
			}
			break;
		} catch (Exception e) {
			System.out.println("数据更新失败");
		}
	}

	private void add() {
		String sql = "INSERT INTO `film_sys`.`movies_info` " +
				"(`name`,`type`,`actor`,`year`) values (?,?,?,?)";
		boolean exit = false;
		while (!exit) {
			try {
				PreparedStatement statement = connection.prepareStatement(sql);
				System.out.println("请输入电影名称 类型 演员 上映年份 (输入q退出录入)");
				for (int i = 1; i <= 4; i++) {
					String temp = scanner.next();
					if (temp == null || temp.isEmpty() || temp.equals("q")) {
						exit = true;
						break;
					}
					if (i == 4) {
						statement.setInt(i, Integer.parseInt(temp));
						continue;
					}
					statement.setString(i, temp);
				}
				System.out.println("添加了" + statement.executeUpdate() + "部电影");
			} catch (SQLException e) {
				System.out.println("录入失败");
			}
		}
	}

	private void delete() throws Exception {
		query();
		String sql = "DELETE FROM `film_sys`.`movies_info` WHERE (`name` = ?) ;";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1,
				reader.readStr("请输入待删除电影的名字"));
		if (0 != statement.executeUpdate())
			System.out.println("删除成功");
		else System.out.println("删除失败");
	}

	private void query(int orderByWhichColumn, int whatTypeToShow) throws Exception {
		String sql = "SELECT * from movies_info where type = ? order by "+cols[orderByWhichColumn - 1]+" desc";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1,
				types[whatTypeToShow - 1].substring(2));
		showQueryResult(statement);
	}

	private void query() throws Exception {
		String sql = "SELECT * FROM movies_info ;";
		PreparedStatement statement = connection.prepareStatement(sql);
		showQueryResult(statement);
	}

	private void rate() throws Exception {
		query();
		String sql = "select * from movies_info where name= ?",
				filmToBeRated = reader.readStr("您想评价哪部电影");
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setString(1, filmToBeRated);
		showQueryResult(statement,false);
		ResultSet set = statement.executeQuery();
		double score = 0, temp;
		int num = 0;
		boolean read = false;
		while (set.next()) {
			read = true;
			score = set.getDouble(5);
			num = set.getInt(6);
		}
		if (!read) {
			System.out.println("查无此片");
			return;
		}
		temp = reader.readDouble("请输入你的评分（满分10分）");
		if (temp < 0 || temp > 10) {
			System.out.println("评分无效");
			return;
		}
		score += temp;
		num++;
		sql = "UPDATE `film_sys`.`movies_info` SET `avg_rating` = ?, `rating_num` = ? WHERE `name` = ? ;";
		statement = connection.prepareStatement(sql);
		statement.setDouble(1, score);
		statement.setInt(2, num);
		statement.setString(3, filmToBeRated);
		statement.executeUpdate();
	}
	private void showQueryResult(PreparedStatement statement)
		throws SQLException{
		showQueryResult(statement,true);
	}
	private void showQueryResult(PreparedStatement statement,boolean  prompt)
			throws SQLException {
		ResultSet set = statement.executeQuery();
		int showCount=0;
		while (set.next()) {
			showCount++;
			System.out.printf("%-18s.\t%-6s.\t%-20s.\t%4d\t",
					set.getString(1),
					set.getString(2),
					set.getString(3),
					set.getInt(4));
			if (set.getInt(6) == 0)
				System.out.println("暂无评价");
			else
				System.out.printf("%4d人已评\t%.1f分\n",
						set.getInt(6),
						set.getDouble(5) /
								set.getInt(6));
		}
		if(prompt){
			if (showCount == 0) System.out.println("没有对应影片");
			else if (showCount <= 3) {
				System.out.println("符合要求的影片过少，试试其他类型吧！");
			}
		}
	}
}